package org.magicsoft.office.excel.impt;

import java.sql.Connection;
import java.sql.Types;

import org.magicsoft.office.db.DatabaseUtils;
import org.magicsoft.office.excel.model.CountData;
import org.magicsoft.office.excel.model.ResultItem;
import org.magicsoft.office.excel.model.Sheet;
import org.magicsoft.office.excel.model.TargetTable;
import org.magicsoft.office.exception.ModelException;

public class ReportDataQuery {

	public void doQuery(Sheet sheet, Connection conn, ResultItem resultItem) {
		StringBuilder sqlBuilder;
		CountData countData;
		for (TargetTable targetTable : sheet.getTargetTables()) {
			countData = new CountData();
			if (targetTable.getPrimaryKey() == null && sheet.getTargetTables().size() > 1) {
				throw new ModelException("当一个sheet对应的表没有指定主键时，不支持一个sheet对应多张表。");
			}

			// 这里认为如果通过一个sheet导入一对多数据，那么一方只有第一条存在nid_和oid_。
			countData.setTableName(targetTable.getDisplayName());

			sqlBuilder = new StringBuilder();
			sqlBuilder.append("select count(*) from ");
			sqlBuilder.append(sheet.getTempTableName());
			sqlBuilder.append(" where ");
			if (targetTable.getPrimaryKey() != null) {
				sqlBuilder.append(targetTable.getOidColumn());
				sqlBuilder.append(" is null and ");
				sqlBuilder.append(targetTable.getNidColumn());
				sqlBuilder.append(" is not null and ");
			}
			sqlBuilder.append("errormsg_ is null");
			countData.setInsertCount((Integer) DatabaseUtils.queryScalar(conn, sqlBuilder.toString(), Types.INTEGER));

			// 只有存在主键才能更新。
			if (targetTable.getPrimaryKey() != null) {
				sqlBuilder = new StringBuilder();
				sqlBuilder.append("select count(*) from ");
				sqlBuilder.append(sheet.getTempTableName());
				sqlBuilder.append(" where ");
				sqlBuilder.append(targetTable.getOidColumn());
				sqlBuilder.append(" is not null and errormsg_ is null");
				countData.setUpdateCount(
						(Integer) DatabaseUtils.queryScalar(conn, sqlBuilder.toString(), Types.INTEGER));
			}
			resultItem.addCountDatas(countData);
		}

		sqlBuilder = new StringBuilder();
		sqlBuilder.append("select errormsg_ from ");
		sqlBuilder.append(sheet.getTempTableName());
		sqlBuilder.append(" where errormsg_ is not null");
		resultItem.setErrorMessge(DatabaseUtils.queryText(conn, sqlBuilder.toString()));
	}

}
